import java.awt.Frame;
import java.io.File;
import java.sql.SQLException;
import java.sql.Types;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import FileHandlers.XlFileDialog;

import com.aspose.cells.Cells;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;
import com.healthmarketscience.jackcess.ColumnBuilder;
import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.Table;
import com.healthmarketscience.jackcess.TableBuilder;

public class ConvertXLS2MDB {
	private Database db;

	public ConvertXLS2MDB(String xlFileName, String outputDbName)
			throws Exception {
		copyWorkBook(xlFileName, outputDbName);
	}

	void copyWorkBook(String xlFileName, String outputDbName) throws Exception {
		Workbook excelWorkbook1 = new Workbook();
		excelWorkbook1.open(xlFileName);
		db = Database.create(new File(outputDbName));
		for (int i = 0; i < excelWorkbook1.getNumberOfSheets(); i++) {
			Worksheet sheet = excelWorkbook1.getWorksheets().getSheet(i);
			if (("GEN_WORK_LOAD,CMU_TRACK,Workload,").contains(sheet.getName())) {
				int numSheets = createNewSheetsFromData(sheet, sheet.getName());
				if (numSheets < 0) {
					System.out.println("No column name Manager");
				}
			}
		}

	}

	@SuppressWarnings("unchecked")
	private int createNewSheetsFromData(Worksheet mySheet, String tabName)
			throws Exception {
		int[] skip = new int[100];
		try {
			if(tabName.toUpperCase().contains("WORK")){
				tabName = "GEN_WORK_LOAD";
			}
			TableBuilder tb = new TableBuilder(tabName);
			Cells cells = mySheet.getCells();
			int columnNum = 0;
			int k = 0;
			for (int i = 0; i <= cells.getMaxColumn(); i++) {
				String columnName = cells.getCell(0, i).getStringValue();
				columnName = skipGaps(columnName);
				if (columnName.length() > 0 && columnName.equals("ID") == false) {
					skip[columnNum] = i;
					tb.addColumn(new ColumnBuilder(columnName).setSQLType(
							Types.VARCHAR).toColumn());
					columnNum++;
				}
			}
			Table newTable = tb.toTable(db);
			if (newTable != null) {
				for (int j = 1; j < cells.getMaxDataRow(); j++) {

					Object[] o = new Object[columnNum];
					for (int i = 0; i < columnNum; i++) {
						k = skip[i];
						String s = cells.getCell(j, k).getStringValue();
						s = s.trim();
						if (s.matches(".*-.*-.*") && isValidDate(s)) {
							s = convertToDate(s);
						} else {
							if (s.length() >= 50)
								s = s.substring(0, 49);
						}
						o[i] = s;
					}
					newTable.addRow(o);
				}

			}
		} catch (Exception e) {
			e.printStackTrace();

		}

		return 0;
	}

	private String convertToDate(String s) {
		try {
			DateFormat df = new SimpleDateFormat("d-MMM-yy");
			DateFormat df1 = new SimpleDateFormat("MM/dd/yyyy");
			Date oldDate = df.parse(s);
			return df1.format(oldDate);
		} catch (ParseException e) {
			return s;
		}
	}

	public boolean isValidDate(String inDate) {
		if (inDate == null)
			return false;
		SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MMM-yy");

		dateFormat.setLenient(false);
		try {
			dateFormat.parse(inDate.trim());
		} catch (ParseException pe) {
			return false;
		}
		return true;
	}

	private String skipGaps(String columnName) {
		if (columnName != null) {
			if (columnName.toUpperCase().contains("TIMES")) {
				columnName = "TIMES_NO";
			}else if (columnName.toUpperCase().contains("2ND")) {
				columnName = "ICN_2ND";
			}
			columnName = columnName.trim().toUpperCase();
			columnName = columnName.replace("  ", " ");
			columnName = columnName.replace(" ", "_");
		}
		return columnName;
	}

	public static void main(String[] args) throws SQLException {
		XlFileDialog ufd = new XlFileDialog();
		String xlFileName = ufd.loadFile(new Frame(), "Open...", "c:\\temp",
				"DB.xls");

		String outputName = ufd.saveFile(new Frame(), "Save...", "c:\\temp",
				"gen2000.mdb");
		try {
			ConvertXLS2MDB input = new ConvertXLS2MDB(xlFileName, outputName);

		} catch (Exception e) {

			e.printStackTrace();
			System.exit(1);
		}
		System.exit(0);
	}
}
